﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using SSISRuntime = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace SSISFromCode
{
    class Sample
    {
        static string INPUT_FILE = "c:\\temp\\eeeMPORT\\bubba.txt";

        //private static string CreateSchemaFile()
        //{
        //    FileInfo fInfo = new FileInfo( fileName );
        //    if ( !fInfo.Exists )
        //    {
        //        throw new ApplicationException( "Source file does not exits." );
        //    }

        //    string schemaFileName = Path.Combine( fInfo.DirectoryName, "schema.ini" );

        //    // If the file already exist
        //    if ( File.Exists( schemaFileName ) )
        //        File.Delete( schemaFileName ); // Delete it
        //    using ( StreamWriter writer = new StreamWriter( schemaFileName ) )
        //    {
        //        // Write apropriate content in the schema.ini file
        //        writer.WriteLine( string.Format( "[{0}]", fInfo.Name ) );
        //        writer.WriteLine( string.Format( "Format=Delimited({0})", "," ) );
        //        writer.WriteLine( "TextDelimiter=\"" );

        //        // Close and save the schema.ini file
        //        writer.Close();
        //    }

        //    return schemaFileName;
        //}

        //private static void DeleteSchemaFile( string fileName )
        //{
        //    FileInfo fInfo = new FileInfo( fileName );
        //    if ( !fInfo.Exists )
        //    {
        //        throw new ApplicationException( "Source file does not exits." );
        //    }

        //    string schemaFileName = Path.Combine( fInfo.DirectoryName, "schema.ini" );

        //    // If the file already exist
        //    if ( File.Exists( schemaFileName ) )
        //        File.Delete( schemaFileName ); // Delete it
        //}

        public static Microsoft.SqlServer.Dts.Runtime.Package Shippit()
        {

            // Create the Package
            Console.WriteLine( "Creating the MySSIS Package" );
            Microsoft.SqlServer.Dts.Runtime.Package myPackage = new Microsoft.SqlServer.Dts.Runtime.Package();
            myPackage.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner100;
            myPackage.Name = "MySSISPackage";
            myPackage.Description = "Created using the SSIS API";
            myPackage.CreatorComputerName = System.Environment.MachineName;
            myPackage.CreatorName = "Devendra";
            myPackage.FailPackageOnFailure = false;
            myPackage.FailParentOnFailure = false;
            myPackage.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.DontSaveSensitive;

            //Add the OLE DB and Flat File Connection Managers
            Console.WriteLine( "Creating the MyOLEDBConnection" );
            ConnectionManager cnOLEDB = myPackage.Connections.Add( "OLEDB" );
            cnOLEDB.Name = "MyOLEDBConnection";
            cnOLEDB.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI; Initial Catalog=CV;Data Source=OMS_ENGG;";
            Console.WriteLine( "Creating the MyFlatFileConnection" );
            ConnectionManager cnFile = myPackage.Connections.Add( "FLATFILE" );
            cnFile.Properties["Name"].SetValue( cnFile, "MyFlatFileConnection" );
            cnFile.Properties["ConnectionString"].SetValue( cnFile, INPUT_FILE );
            cnFile.Properties["Format"].SetValue( cnFile, "Delimited" );
            cnFile.Properties["ColumnNamesInFirstDataRow"].SetValue( cnFile, true );
            cnFile.Properties["DataRowsToSkip"].SetValue( cnFile, 0 );
            cnFile.Properties["HeaderRowDelimiter"].SetValue( cnFile, "\n" );
            cnFile.Properties["RowDelimiter"].SetValue( cnFile, "\n" );
            cnFile.Properties["TextQualifier"].SetValue( cnFile, "\"" );

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='text;HDR=Yes;FMT=Delimited';Data Source=";
            //DataTable schemaTable = null;                   // DataTable instance
            FileInfo fInfo = new FileInfo( INPUT_FILE );    // File informations

            // Make the connection string
            string conStr = connectionString + fInfo.DirectoryName;

            // Create the schema file in order to read thru Ole
            //string SchemaFileName = CreateSchemaFile();

            // Create a connection objct and open it
            //using ( OleDbConnection connection = new OleDbConnection( conStr ) )
            //{
            //    connection.Open();
            //    // Create a query to read the OleDb source
            //    string commandText = string.Format( "SELECT * FROM [{0}]", fInfo.Name );
            //    using ( OleDbCommand command = new OleDbCommand( commandText, connection ) )
            //    {
            //        // Execute the query and get the DataReader
            //        using ( IDataReader reader = command.ExecuteReader() )
            //        {
            //            // get the schema TABLE
            //            schemaTable = reader.GetSchemaTable();
            //        }
            //    }
            //    connection.Close(); // close the connection
            //}

            // delete the schema file
            //DeleteSchemaFile( SchemaFileName );

            // get the actuall connection manger instance
            IDTSConnectionManagerFlatFile100 flatFileConnection = cnFile.InnerObject as IDTSConnectionManagerFlatFile100;
            //SSISRuntime.IDTSConnectionManagerFlatFileColumn100 column;
            //SSISRuntime.IDTSName100 name;

            for ( int i = 0; i < 2; i++ )
            {
                var c = flatFileConnection.Columns.Add();
                if ( i == 1 )
                    c.ColumnDelimiter = "\n";
                else
                    c.ColumnDelimiter = ",";
                c.TextQualified = true;
                c.ColumnType = "Delimited";
                c.DataType = DataType.DT_WSTR;
                c.DataPrecision = 0;
                c.DataScale = 0;
                ((IDTSName100)c).Name = i.ToString();
            }

            //Title,ISBN,MSRP,Author,Slots,Publisher,PublicationDate,Rank
            //foreach ( DataRow row in schemaTable.Rows )
            //{ // iterate
            //    string colName = row["ColumnName"] as string; // get the col name

            //    // now create a new column for the connection manager
            //    column = flatFileConnection.Columns.Add(); // if this is the last row
            //    if ( schemaTable.Rows.IndexOf( row ) == ( schemaTable.Rows.Count - 1 ) )
            //        column.ColumnDelimiter = "\n";

            //    // add the row delimiter
            //    else
            //        column.ColumnDelimiter = ",";
            //    column.TextQualified = true;
            //    column.ColumnType = "Delimited";
            //    column.DataType = DataType.DT_WSTR; // Apparently I am giving this..I need to do Rnd no this for clear idea
            //    column.DataPrecision = 0;
            //    column.DataScale = 0;
            //    name = (IDTSName100)column;
            //    name.Name = colName;
            //}

            // get the sql task type
            Type taskType = typeof( ExecuteSQLTask );

            // cteate a task of type ExecuteSQLTask
            //myPackage.Executables.Add("DTS.Pipeline");
            Executable executable = myPackage.Executables.Add( taskType.AssemblyQualifiedName );

            // now configuring the new task
            Microsoft.SqlServer.Dts.Runtime.TaskHost taskHost1 = executable as Microsoft.SqlServer.Dts.Runtime.TaskHost; // get the Task host instance
            taskHost1.Name = "Create sql table";

            ////Add a Data Flow Task
            var sqlTask = taskHost1.InnerObject as ExecuteSQLTask;// get the sql task from the host
            sqlTask.Connection = cnOLEDB.Name; // set the connection manager
            sqlTask.SqlStatementSource = "CREATE TABLE [dbo].[TestImportTable]([Title] [nvarchar](255),[ISBN] [nvarchar](255),[MSRP] [nvarchar](255),[Author] [nvarchar](255),[Slots] [nvarchar](255),[Publisher] [nvarchar](255),[PublicationDate] [nvarchar](255),[Rank] [nvarchar](255) )";

            //sqlDataDestination.GetDestinationTableCreationSql(schemaTable.GetSchemaTable());
            Executable dataflowExecutable = myPackage.Executables.Add( "DTS.Pipeline.1" );
            Microsoft.SqlServer.Dts.Runtime.TaskHost taskHost = dataflowExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost; // get the task host
            MainPipe mainPipeInstance = taskHost.InnerObject as MainPipe; // get the pipe instance
            IDTSComponentMetaData100 dataflowComponent;//Source Adapter

            // now create the component
            dataflowComponent = mainPipeInstance.ComponentMetaDataCollection.New();

            // set the name
            dataflowComponent.Name = "Flat File Source";

            // set the component class ID
            dataflowComponent.ComponentClassID = "DTSAdapter.FlatFileSource";

            CManagedComponentWrapper managedFlatFileInstance = dataflowComponent.Instantiate();

            // get the populate the properties
            managedFlatFileInstance.ProvideComponentProperties();

            // putting the connection
            if ( dataflowComponent.RuntimeConnectionCollection.Count > 0 )
            { // If connection is necessary
                dataflowComponent.RuntimeConnectionCollection[0].ConnectionManagerID = cnFile.ID;
                dataflowComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface( cnFile );
            }

            // establish a connection
            managedFlatFileInstance.AcquireConnections( null );

            // Initialize the metadata
            managedFlatFileInstance.ReinitializeMetaData();


            // create the mapping now
            IDTSExternalMetadataColumn100 exOutColumn;
            foreach ( IDTSOutputColumn100 outColumn in dataflowComponent.OutputCollection[0].OutputColumnCollection )
            { // create the MAP
                exOutColumn = dataflowComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
                // map it
                managedFlatFileInstance.MapOutputColumn( dataflowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true );
            }

            // Release the connection now
            managedFlatFileInstance.ReleaseConnections();
            IDTSComponentMetaData100 DestdataflowComponent;//Destination Adapter

            // now create the component
            DestdataflowComponent = mainPipeInstance.ComponentMetaDataCollection.New();

            // set the name
            DestdataflowComponent.Name = "OLEDB Source";

            // set the component class ID
            //dataflowComponent.ComponentClassID = "DTSAdapter.FlatFileSource";
            DestdataflowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

            // Before going thru the initialization we need to craete the destination table
            // because the SSIS object model will try to access that table fore reading the metadata
            //sqlDataDestination.CreateDataStore(ssisSource.DataSource.GetSchemaTable());
            String cnnStrCreateTable = "Data Source=OMS_ENGG;Integrated Security=True;User Id=sa;Password=welcome;Initial Catalog=CV";
            using ( SqlConnection connection = new SqlConnection( cnnStrCreateTable ) )
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                //return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
                // Create a command and prepare it for execution
                SqlCommand cmd = new SqlCommand();

                // Associate the connection with the command
                cmd.Connection = connection;

                // Set the command text (stored procedure name or SQL statement)
                cmd.CommandText = "CREATE TABLE [dbo].[TestImportTable]([Title] [nvarchar](255),[ISBN] [nvarchar](255),[MSRP] [nvarchar](255),[Author] [nvarchar](255),[Slots] [nvarchar](255),[Publisher] [nvarchar](255),[PublicationDate] [nvarchar](255),[Rank] [nvarchar](255) )";

                // Set the command type
                cmd.CommandType = CommandType.Text;
                int retval = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                connection.Close();
            }

            CManagedComponentWrapper managedOleInstance = DestdataflowComponent.Instantiate();

            // populate the properties
            managedOleInstance.ProvideComponentProperties();

            // setting the connection
            if ( DestdataflowComponent.RuntimeConnectionCollection.Count > 0 )
            { // If connection is necessary
                DestdataflowComponent.RuntimeConnectionCollection[0].ConnectionManagerID = cnOLEDB.ID;
                DestdataflowComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface( cnOLEDB );
            }

            // Set the custom properties.
            managedOleInstance.SetComponentProperty( "AccessMode", 0 ); // Table of View mode
            managedOleInstance.SetComponentProperty( "AlwaysUseDefaultCodePage", false ); // Default Codepage
            managedOleInstance.SetComponentProperty( "DefaultCodePage", 1252 ); // Set it
            managedOleInstance.SetComponentProperty( "FastLoadKeepIdentity", false ); // Fast load
            managedOleInstance.SetComponentProperty( "FastLoadKeepNulls", false );
            managedOleInstance.SetComponentProperty( "FastLoadMaxInsertCommitSize", 0 );
            managedOleInstance.SetComponentProperty( "FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS" );
            managedOleInstance.SetComponentProperty( "OpenRowset", string.Format( "[{0}].[dbo].[{1}]", "CV", "TestImportTable" ) );

            // now create the path
            IDTSPath100 path = mainPipeInstance.PathCollection.New();
            path.AttachPathAndPropagateNotifications( dataflowComponent.OutputCollection[0], DestdataflowComponent.InputCollection[0] );

            //CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.ComponentInstance;
            // Now activate a connection and create the mappings
            // =========================================================================
            // Establish a connection
            managedOleInstance.AcquireConnections( null );

            // initialize the metadata
            managedOleInstance.ReinitializeMetaData();

            // Get the destination's default input and virtual input.
            IDTSInput100 input = DestdataflowComponent.InputCollection[0];
            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            // Iterate through the virtual input column collection.
            foreach ( IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection )
            {
                managedOleInstance.SetUsageType(
                input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY );
            }

            IDTSExternalMetadataColumn100 exColumn;
            foreach ( IDTSInputColumn100 inColumn in DestdataflowComponent.InputCollection[0].InputColumnCollection )
            { // create the map
                exColumn = DestdataflowComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
                exColumn.Name = inColumn.Name;
                managedOleInstance.MapInputColumn( DestdataflowComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID );
            }

            // Now release the connection
            managedOleInstance.ReleaseConnections();
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml( "C:\\MyDTSPackage.dtsx", myPackage, null );

            return myPackage;

            // execute this package
            //Microsoft.SqlServer.Dts.Runtime.DTSExecResult res = myPackage.Execute();

            //// The result that will be given to the user
            //if ( res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success )
            //{ // If it was success
            //    Console.WriteLine( "Package Executed Successfully" );
            //}
            //else
            //{
            //    Console.WriteLine( "Package Did Not Executed Successfully" );
            //}

            //Console.ReadKey();
        }


        private void CreateFlatFileSource( Microsoft.SqlServer.Dts.Runtime.Package package, Executable dft, string componentName, ConnectionManager cm )
        {

            // See also ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/c06065cf-43e5-4b6b-9824-7309d7f5e35e.htm

            var th = (Microsoft.SqlServer.Dts.Runtime.Wrapper.TaskHost)dft;
            var mp = (MainPipe)th.InnerObject;
            IDTSComponentMetaData100 ffs = mp.ComponentMetaDataCollection.New();
            ffs.ComponentClassID = "DTSAdapter.FlatFileSource.1";

            // Get the design time instance of the component.
            CManagedComponentWrapper instance = ffs.Instantiate();

            // Initialize the component
            instance.ProvideComponentProperties();

            // These have to be set *after* ProvideComponetProperties() is called -- or their values are lost. :-(
            ffs.Name = componentName;
            ffs.Description = componentName;

            // Specify the connection manager.
            ffs.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface( cm );
            ffs.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[cm.Name].ID;

            // Setup the custom component properties.
            instance.SetComponentProperty( "RetainNulls", true ); // Treat empty columns as null.

            // Reinitialize the metadata.
            // HACK Does this require a live connection to a valid schema? Yes, unfortunately.
            instance.AcquireConnections( null );
            instance.ReinitializeMetaData();
            instance.ReleaseConnections();

            // Set FastParse where appropriate? They can't be set until after the metadata voodoo?
            foreach ( IDTSOutputColumn100 outcol in ffs.OutputCollection["Flat File Source Output"].OutputColumnCollection )
            {
                switch ( outcol.DataType )
                {
                    case DataType.DT_BYTES:
                    case DataType.DT_EMPTY:
                    case DataType.DT_IMAGE:
                    case DataType.DT_NTEXT:
                    case DataType.DT_NULL:
                    case DataType.DT_STR:
                    case DataType.DT_TEXT:
                    case DataType.DT_WSTR:
                        // Skip the text types because FastParse cannot be set for them.
                        break;
                    default:
                        outcol.CustomPropertyCollection["FastParse"].Value = true;
                        break;
                }
            }
            // return ffs;
        }
    
    
    
    }
}
